package edu.upenn.cis.cis550.hw3.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import edu.upenn.cis.cis550.hw3.client.QueryRecom;
import edu.upenn.cis.cis550.hw3.server.RelationBuilder;
import edu.upenn.cis.cis550.hw3.shared.Relation;

/**
 * This class returns all users recommendation as a Relation to the client 
 * 
 * @author 
 * 
 */
public class QueryRecomImpl extends RemoteServiceServlet implements
        QueryRecom {
    Connection connection;

    private static final long serialVersionUID = 1L;

    /**
     * Constructor -- initialize our source tables and the object
     */
    public QueryRecomImpl() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String serverName = "fling.seas.upenn.edu:3306";
            String database = "silviamo";
            String url = "jdbc:mysql://" + serverName + "/" + database;
            String user = "silviamo";
            String password = "dbproject";

            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }
    

    
    /**
     * Call if isItProfessor returns true, that way the advisor could be recommended if it isn't friend
     */
    public Relation getAdviseeRecommendations(String professorid) throws IllegalArgumentException {
    	try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select A.studentID from Advises A where A.professorID = " + professorid + " and A.studentID not in FriendIDs")) {         	
                ResultSet rs = statement.getResultSet();
                return  RelationBuilder.createRelation("AdviseeRecommendation", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * Call if isItProfessor returns true, that way the advisor could be recommended if it isn't friend
     */
    public Relation getAdvisorRecommendations(String studentid) throws IllegalArgumentException {
    	try {
            Statement statement = connection.createStatement();
            if (statement
                    .execute("select A.professorID from Advises A where A.studentID = " + studentid + " and A.professorID not in FriendIDs")) {      	
                ResultSet rs = statement.getResultSet();
                return RelationBuilder.createRelation("AdvisorRecommendations", rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    } 
    
    /**
     * Mutual all recommendations to the User
     */
    public Relation getAllFriendRecom(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            statement.execute("create or replace view Recom as Select Friend.friendID as recomID from FriendIDs, Friend where Friend.userID = FriendIDs.friendID and " +
                    		"Friend.friendID <>" + userid + " and Friend.friendID not in (select friendID from FriendIDs) union all " +
                    				"select c.userID as recomID from Comment c, Photo p where c.photoID=p.photoID and " +
                    				"c.userID <> p.userID and p.userID =" + userid + " and c.userID not in (select friendID from FriendIDs)");            	
            statement.execute("create or replace view ScoreRecom as select R.recomID, count(*) as rank from Recom R group by R.recomID order by rank desc");
                if(statement.execute("select S.recomID, S.rank, U.firstName, U.lastName from ScoreRecom S, User U where S.recomID = U.userID")) {
            	ResultSet rs = statement.getResultSet();
            	System.out.println("about to build relation");
            	Relation r = RelationBuilder.createRelation("All Friend Recommendation", rs);
                 return r;
                }
            
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * Friends of friends
     */
    public Relation getFriendsOfFriends(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();
            statement.execute("select Friend.friendID as recomID from FriendIDs, Friend where Friend.userID = FriendIDs.friendID and " +
                    		"Friend.friendID <>" + userid);
            
            	ResultSet rs = statement.getResultSet();
            	Relation r = RelationBuilder.createRelation("Get Friends of friends", rs);
                return r;
            
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * Mutual friends recommended to the User
     */
    public Relation getMutualFriendRecom(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("Select Friend.friendID, count(*) as rank FriendIDs, Friend where Friend.userID = FriendIDs.friendID " +
                    				"and Friend.friendID <>" + userid+ " group by Friend.friendID order by rank desc")) {            	
            	 ResultSet rs = statement.getResultSet();
                 return RelationBuilder.createRelation("Mutual Friend Recommendation", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * To get recommendation based on photo rating 
     */
    public Relation getPhotoRatingRecom(String userid) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select c.userID as commentor from Comment c, Photo p where " +
                    		"c.photoID=p.photoID and c.userID <> p.userID and p.userID = "+ userid+ " " +
                    				"and c.userID not in (Select friendID from FriendIDs)")) {            	
            	 ResultSet rs = statement.getResultSet();
                 return RelationBuilder.createRelation("Common raters for a photo recommended as friends", rs);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }   
    
}
